package com.jsb.laundry.common.utils.excel;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * 用于导出Excel报表。
 *
 * @author caipeichao
 */
public class ExcelResponse extends AttachmentResponse {

    private HSSFWorkbook        wb;
    private HSSFSheet           currSheet;
    private boolean             dumped;
    private static final Logger LOGGER             = LoggerFactory.getLogger(ExcelResponse.class);
    public static final int     MAX_EXCEL_LINE_NUM = 65535;

    public ExcelResponse(HttpServletRequest request, HttpServletResponse response){
        this(request, response,null);
    }

    /**
     * @param request
     * @param response
     * @param filename 返回给浏览器的文件名。
     */
    public ExcelResponse(HttpServletRequest request, HttpServletResponse response, String filename){
        super(request, response, filename, "application/msexcel");
        wb = new HSSFWorkbook();
        dumped = false;
    }

    /**
     * 切换到指定的sheet上面进行后面的操作
     *
     * @param sheetname
     */
    public void selectSheet(String sheetname) {
        HSSFSheet sheet = wb.getSheet(sheetname);
        if (null == sheet) {
            sheet = wb.createSheet(sheetname);
        }

        this.currSheet = sheet;
    }

    /**
     * 切换到指定数字索引的sheet上面进行后面的操作
     *
     * @param index
     */
    public HSSFSheet selectSheet(int index) {
        int sheets = wb.getNumberOfSheets();
        // 所选序号恰好超过已存在序号一位，则表示创建一个新的sheet
        if (index == sheets) {
            wb.createSheet();
        }

        currSheet = wb.getSheetAt(index);
        return currSheet;
    }

    /**
     * 字段标题
     */
    public void writeTitles(Object[] titles) {
        writeRecord(titles);
    }

    /**
     * 字段数据
     */
    public void writeRecord(Object... cellObjs) {
        // 如果为空表示没有sheet,则创建一个
        if (currSheet == null) {
            this.selectSheet(0);
        }

        HSSFRow row = currSheet.createRow(currSheet.getPhysicalNumberOfRows());
        for (int i = 0; i < cellObjs.length; i++) {
            HSSFCell cell = row.createCell(i);
            Object o = cellObjs[i];
            if (o == null) {
                cell.setCellValue((String) null);
                continue;
            }

            Class<?> clazz = o.getClass();

            if (String.class.equals(clazz)) {
                cell.setCellValue((String) o);
            } else if (Double.class.equals(clazz)) {
                cell.setCellValue((Double) o);
            } else if (Boolean.class.equals(clazz)) {
                cell.setCellValue((Boolean) o);
            } else if (Calendar.class.equals(clazz)) {
                cell.setCellValue((Calendar) o);
            } else if (RichTextString.class.equals(clazz)) {
                cell.setCellValue((RichTextString) o);
            } else if (Date.class.equals(clazz)) {
                cell.setCellValue((Date) o);
            } else if (Integer.class.equals(clazz)) {
                cell.setCellValue((Integer) o);
            } else {
                cell.setCellValue(String.valueOf(o));
            }
        }
    }






    /**
     * 字段数据 部分颜色改变红色
     */
    public static void writeRecordPartColor(HSSFSheet currSheet,List<Integer> changeIndexs,String changeComment,HSSFWorkbook wb,Object... cellObjs) {
        HSSFRow row = currSheet.createRow(currSheet.getPhysicalNumberOfRows());
        int changeIndexnote = 0 ;
        for (int i = changeIndexnote; i < cellObjs.length; i++) {
            HSSFCell cell = row.createCell(i);
            Object o = cellObjs[i];
            for(int changeIndex : changeIndexs) {
                if (o == null) {
                    cell.setCellValue((String) null);
                    continue;
                }


                Class<?> clazz = o.getClass();
                if (i == changeIndex) {
                    String displayName = o.toString();
                    HSSFFont font = wb.createFont();
                    font.setColor(HSSFColor.RED.index);
                    HSSFRichTextString ts = new HSSFRichTextString(displayName);
                    // 从4开始
                    ts.applyFont(displayName.length()-changeComment.length(),displayName.length(),font);
                    cell.setCellValue(ts);
                    changeIndexnote = changeIndex;
                    break;
                } else {
                    if (String.class.equals(clazz)) {
                        cell.setCellValue((String) o);
                    }
                }
            }
        }
    }


    /**
     * 生成批注
     * @param sheet
     * @param commentStr
     * @return
     */
    public static HSSFComment generateComment(HSSFSheet sheet, String commentStr) {
        HSSFPatriarch patr = sheet.createDrawingPatriarch();
        HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 14, 1, 18, 3);
        HSSFComment comment = patr.createCellComment(anchor);
        comment.setString(new HSSFRichTextString(commentStr));
        comment.setVisible(false);
        return comment;
    }
    /**
     * 生成批注
     * @param sheet
     * @param commentStr
     * @return
     */
    public static HSSFComment generateCommentTest(HSSFSheet sheet, String commentStr) {
        HSSFPatriarch patr = sheet.createDrawingPatriarch();
        HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 14, 1, 22, 4);
        HSSFComment comment = patr.createCellComment(anchor);
        comment.setString(new HSSFRichTextString(commentStr));
        comment.setVisible(false);
        return comment;
    }

    /**
     * 只有close才能生效
     */
    @Override
    public void close() {
        if (!dumped) {
            dump();
        }

        try {
            super.close();
        } catch (IOException e) {
            // do nothing
            LOGGER.warn("super close failed.", e);
        }
    }

    public void dump() {
        try {
            wb.write(this);
        } catch (IOException e) {
            LOGGER.warn("excel dump failed.", e);
        }
    }

    public HSSFCellStyle writeStyle() {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return style;
    }

    public HSSFFont writeFont() {
        HSSFFont font = wb.createFont();
        return font;
    }

    public HSSFSheet createSheet(String sheetname) {
        selectSheet(sheetname);
        return currSheet;
    }

    public void createFile(File file) {
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(file);
            // 输出到文件
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            LOGGER.error("file create is error", e);
        }
    }

    public HSSFRow createRow(int rowIndex, int columCount) {
        HSSFRow row = currSheet.createRow(rowIndex);
        for (int i = 0; i < columCount; i++) {
            row.createCell(i);
        }
        return row;
    }

    public ExcelResponse(HttpServletRequest request, HttpServletResponse response, String filename, InputStream is){
        super(request, response, filename, "application/msexcel");
        try {
            wb = new HSSFWorkbook(is);
            dumped = false;
        } catch (Exception e) {
            LOGGER.error("获取excel手工模版失败：", e);
        } finally {
            IOUtils.closeQuietly(is);
        }
    }

    public ExcelResponse(HttpServletRequest request, HttpServletResponse response, String filename, String url){
        super(request, response, filename, "application/msexcel");
        try {
            InputStream is = new FileInputStream(url);
            wb = new HSSFWorkbook(is);
            dumped = false;
            IOUtils.closeQuietly(is);
        } catch (Exception e) {
            LOGGER.error("获取excel手工模版失败：", e);
        }
    }

    public HSSFWorkbook wb() {
        return wb;
    }

    /**
     * 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
     *
     * @param textlist
     * @param firstRow
     * @param endRow
     * @param firstCol
     * @param endCol
     * @return
     */
    public void setHSSFValidation(String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        HSSFCell cell = currSheet.createRow(firstRow).createCell(firstCol);
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        currSheet.addValidationData(dataValidationList);
    }

    /**
     * 设置指定表的名称
     * 
     * @param index
     * @param name
     */
    public void setSheetName(int index, String name) {
        wb.setSheetName(index, name);
    }
}
